﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.IO;

namespace NETXPF.Library
{
    /// <summary>
    /// A set of high-level wrappers around the ADO.NET framework.
    /// This makes it easier to enforce best practices such as preventing SQL injection by using parameters.
    /// </summary>
    public class Database
    {
        public DbProviderFactory Factory
        {
            get;
            set;
        }
        public Database(DbProviderFactory dbfactory)
        {
            Factory = dbfactory;
        }
        public DbConnection Connect(string connString)
        {
            DbConnection conn = Factory.CreateConnection();
            conn.ConnectionString = connString;
            if (conn.State != System.Data.ConnectionState.Open)
                conn.Open();
            return conn;
        }
        public DbDataReader ExecuteReader(DbConnection conn, string cmdText)
        {
            return ExecuteReader(conn, cmdText, null, null);
        }
        private void AddParams(DbCommand cmd, string[] paramNames, object[] paramValues)
        {
            if (paramNames == null)
                return;
            Dictionary<string, object> paramNameValues = new Dictionary<string, object>();
            for (int i = 0; i < paramNames.Length; i++)
            {
                paramNameValues.Add(paramNames[i], paramValues[i]);
            }
            foreach (string pn in paramNames)
            {
                DbParameter param = Factory.CreateParameter();
                param.ParameterName = pn;
                param.Value = paramNameValues[pn];
                SetParamType(param);
                cmd.Parameters.Add(param);
            }
        }

        private void SetParamType(DbParameter param)
        {
            param.DbType = GetDbType(param.Value);
            if (param.DbType == System.Data.DbType.Binary)
            {
                param.Size = ((byte[])param.Value).Length;
            }
            if (param.DbType == System.Data.DbType.String)
            {
                param.Size = ((string)param.Value).Length;
            }
        }

        private System.Data.DbType GetDbType(object o)
        {
            if (o is DateTime)
            {
                return System.Data.DbType.DateTime;
            }
            if (o is decimal)
            {
                return System.Data.DbType.Decimal;
            }
            if (o is double)
            {
                return System.Data.DbType.Double;
            }
            if (o is float)
            {
                return System.Data.DbType.Single;
            }
            if (o is long)
            {
                return System.Data.DbType.Int64;
            }
            if (o is int)
            {
                return System.Data.DbType.Int32;
            }
            if (o is short)
            {
                return System.Data.DbType.Int16;
            }
            if (o is bool)
            {
                return System.Data.DbType.Boolean;
            }
            if (o is byte)
            {
                return System.Data.DbType.Byte;
            }
            if (o is string)
            {
                return System.Data.DbType.String;
            }
            if (o is byte[])
            {
                return System.Data.DbType.Binary;
            }
            return System.Data.DbType.Object;
        }
        public DbCommand PrepareCommand(DbConnection conn, string cmdText, string[] paramNames, object[] paramValues, DbTransaction trans)
        {
            DbCommand cmd = Factory.CreateCommand();
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = cmdText;
            AddParams(cmd, paramNames, paramValues);
            cmd.Prepare();
            return cmd;
        }
        public DbCommand PrepareCommand(DbConnection conn, string cmdText, string[] paramNames, DbTransaction trans)
        {
            return PrepareCommand(conn, cmdText, paramNames, new object[paramNames.Length], trans);
        }
        public void SetParams(DbCommand cmd, object[] paramValues)
        {
            for (int i = 0; i < paramValues.Length; i++)
            {
                cmd.Parameters[i].Value = paramValues[i];
                SetParamType(cmd.Parameters[i]);
            }
        }
        public DbDataReader ExecuteReader(DbConnection conn, string cmdText, string[] paramNames, object[] paramValues)
        {
            if (conn.State == System.Data.ConnectionState.Closed || conn.State == System.Data.ConnectionState.Broken)
                conn.Open();
            return PrepareCommand(conn, cmdText, paramNames, paramValues, null).ExecuteReader();
        }
        public int ExecuteNonQuery(DbConnection conn, string cmdText, string[] paramNames, object[] paramValues)
        {
            if (conn.State == System.Data.ConnectionState.Closed || conn.State == System.Data.ConnectionState.Broken)
                conn.Open();
            return PrepareCommand(conn, cmdText, paramNames, paramValues, null).ExecuteNonQuery();
        }
    }
}
